package com.crm.business.crm.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.crm.business.admin.service.AdminService;
import com.crm.business.crm.mapper.CrmFieldMapper;
import com.crm.business.crm.mapper.CrmInterfaceErrorInfoLogMapper;
import com.crm.business.crm.mapper.CrmPutinStatisticsMapper;
import com.crm.business.crm.mapper.CrmSourceMapper;
import com.crm.business.crm.service.CrmPageService;
import com.crm.business.crm.service.ICrmContractService;
import com.crm.business.crm.service.ICrmLeadsService;
import com.crm.business.crm.service.ICrmPutinStatisticsService;
import com.crm.common.crm.common.CrmModel;
import com.crm.common.crm.constant.CrmEnum;
import com.crm.common.crm.constant.FieldEnum;
import com.crm.common.crm.entity.BO.CrmRoiSearchBO;
import com.crm.common.crm.entity.BO.CrmUpdateInformationBO;
import com.crm.common.crm.entity.PO.CrmField;
import com.crm.common.crm.entity.PO.CrmInterfaceErrorInfoLog;
import com.crm.common.crm.entity.PO.CrmPutinStatistics;
import com.crm.common.crm.entity.VO.CrmFieldSortVO;
import com.crm.common.crm.entity.VO.CrmModelFiledVO;
import com.crm.common.ka.core.entity.BasePage;
import com.crm.business.servlet.ApplicationContextHolder;
import com.crm.business.servlet.BaseServiceImpl;
import com.crm.business.utils.UserUtil;
import com.crm.common.utils.StringUtils;
import com.crm.common.utils.spring.SpringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

@Service
@Slf4j
public class CrmPutinStatisticsServiceImpl extends BaseServiceImpl<CrmPutinStatisticsMapper, CrmPutinStatistics> implements ICrmPutinStatisticsService, CrmPageService {

    private static final ExecutorService THREAD_POOL = new ThreadPoolExecutor(1, 10, 5L, TimeUnit.SECONDS, new LinkedBlockingDeque<>(2048), new ThreadPoolExecutor.AbortPolicy());

    @Autowired
    private CrmPutinStatisticsMapper crmPutinStatisticsMapper;

    @Autowired
    private CrmInterfaceErrorInfoLogMapper crmInterfaceErrorInfoLogMapper;

    @Autowired
    private CrmFieldMapper crmFieldMapper;

    @Autowired
    private ICrmPutinStatisticsService crmPutinStatisticsService;

    @Autowired
    private AdminService adminService;

    @Autowired
    private CrmSourceMapper crmSourceMapper;

    @Override
    public String[] appendSearch() {
        return new String[0];
    }

    @Override
    public void setOtherField(Map<String, Object> map) {

    }

    @Override
    public CrmEnum getLabel() {
        return null;
    }

    @Override
    public List<CrmModelFiledVO> queryDefaultField() {
        return null;
    }

    @Override
    public CrmModel queryById(Integer id) {
        return null;
    }

    @Override
    public void deleteByIds(List<Integer> ids) {
        removeByIds(ids);
    }

    @Override
    public BasePage<CrmPutinStatistics> queryPageList(CrmRoiSearchBO search) {
        Integer pageStart = search.getPage() > 0 ? (search.getPage() - 1) * search.getLimit() : 0;
        search.setPageStart(pageStart);
        if (StringUtils.isNotBlank(search.getPutinPlatform())) {
            String allPutinPlatform = search.getPutinPlatform();
            search.setPutinPlatforms(allPutinPlatform.split(","));
        }
        Integer count = getBaseMapper().queryPageListCount(search);
        if(count == 0){
            BasePage<CrmPutinStatistics> basePage = new BasePage<>(search.getPage(),search.getLimit());
            return basePage;
        }

        /*if(ObjectUtil.isEmpty(search.getDeptIds())){
            List<Integer> deptIdList = new ArrayList<>();
            deptIdList.addAll(adminService.queryChildDeptId(UserUtil.getUser().getDeptId()).getData());
            deptIdList.add(UserUtil.getUser().getDeptId());
            search.setDeptIds(deptIdList);
        }*/

        List<Integer> deptIdList = new ArrayList<>();
        List<Integer> deptIdValueList = new ArrayList<>();
        deptIdList.addAll(adminService.queryChildDeptId(14861));
        deptIdList.add(14861);
        if(ObjectUtil.isEmpty(search.getDeptIds())){
            if (deptIdList.stream().filter(w-> String.valueOf(w).equals(String.valueOf(UserUtil.getUser().getDeptId()))).findAny().isPresent()){
                //说明有
                deptIdValueList.addAll(adminService.queryChildDeptId(UserUtil.getUser().getDeptId()));
                deptIdValueList.add(UserUtil.getUser().getDeptId());
                search.setDeptIds(deptIdValueList);
            }else{
                //TODO 默认展示总部信息
                search.setDeptIds(deptIdList);
            }
        }else{
            for(int i=0;i<deptIdList.size();i++){
                for (int j=0;j<search.getDeptIds().size();j++){
                    if(search.getDeptIds().get(j).equals(deptIdList.get(i))){
                        deptIdValueList.add(search.getDeptIds().get(j));
                    }
                }
            }
            search.setDeptIds(deptIdValueList);
            if(deptIdValueList.size() == 0){
                BasePage<CrmPutinStatistics> basePage = new BasePage<>(search.getPage(),search.getLimit());
                return basePage;
            }
        }

        // 根据部门获取到用户后根据用户查询合同回款信息
        List<Long> userIdList = new ArrayList<>();
        userIdList.addAll(adminService.queryUserByDeptIds(search.getDeptIds()));
        search.setUserIds(userIdList);

        List<CrmPutinStatistics> crmPutinStatisticsList = getBaseMapper().queryPageList(search);
        List<CrmPutinStatistics> contractList=getBaseMapper().queryByPlatfrom(search);
        List<CrmPutinStatistics> noTimeMoney= getBaseMapper().queryAllNoTimeMoney(search);

        CrmPutinStatistics summoney=new CrmPutinStatistics();
        //合同回款金额
        summoney.setReceivedMoney(BigDecimal.valueOf(0));
        // 回款成交金额
        summoney.setReceivablesDealMoney(BigDecimal.valueOf(0));
        //已成交合同的全部金额
        summoney.setContractAllMoney(BigDecimal.valueOf(0));
        // 已成交合同的全部金额(没有时间限制)
        summoney.setContractAllMoneyNotime(BigDecimal.valueOf(0));
        // 已审核通过的回款金额(没有时间限制)
        summoney.setReceivablesDealMoneyNoTime(BigDecimal.valueOf(0));
        // 实际消耗金额
        summoney.setActualConsumptionMoney(BigDecimal.valueOf(0));
        // 渠道消耗金额
        summoney.setPracticalPutinMoney(BigDecimal.valueOf(0));

        //获取合同,回款,实际消耗金额
        for (int i=0;i<crmPutinStatisticsList.size();i++){
            // 渠道消耗金额减返点金额
            BigDecimal money1 = crmPutinStatisticsList.get(i).getPracticalPutinMoney().subtract(crmPutinStatisticsList.get(i).getPutinRebatesMoney());
            // 减赔付金额
            BigDecimal money2 = money1.subtract(crmPutinStatisticsList.get(i).getPutinCompensateMoney());
            // 减转出金额
            BigDecimal money3 = money2.subtract(crmPutinStatisticsList.get(i).getRolloutMoney());
            // 加转入金额
            BigDecimal consumeMoney = money3.add(crmPutinStatisticsList.get(i).getRollinMoney());
            // 得到实际消耗金额
            crmPutinStatisticsList.get(i).setActualConsumptionMoney(consumeMoney);

            for(int j=0;j<contractList.size();j++){
                if(crmPutinStatisticsList.get(i).getPutinPlatform().equals(contractList.get(j).getPutinPlatform())){
                    // 合同回款金额
                    crmPutinStatisticsList.get(i).setReceivedMoney(contractList.get(j).getReceivedMoney());
                    // 合同成交数量
                    crmPutinStatisticsList.get(i).setContractDealCount(contractList.get(j).getContractDealCount());
                    // 回款成交金额
                    crmPutinStatisticsList.get(i).setReceivablesDealMoney(contractList.get(j).getReceivablesDealMoney());
                    // 已成交合同的全部金额
                    crmPutinStatisticsList.get(i).setContractAllMoney(contractList.get(j).getContractAllMoney());
                }
            }
            for (int j=0;j<noTimeMoney.size();j++){
                if (noTimeMoney.get(j).getPutinPlatform()!=null && noTimeMoney.get(j).getPutinPlatform().equals(crmPutinStatisticsList.get(i).getPutinPlatform())){
                    // 已成交合同的全部金额(没有时间限制)
                    crmPutinStatisticsList.get(i).setContractAllMoneyNotime(noTimeMoney.get(j).getContractAllMoneyNotime());
                    // 已审核通过的回款金额(没有时间限制)
                    crmPutinStatisticsList.get(i).setReceivablesDealMoneyNoTime(noTimeMoney.get(j).getReceivablesDealMoneyNoTime());
                }
            }

            if (crmPutinStatisticsList.get(i).getReceivedMoney()==null){
                crmPutinStatisticsList.get(i).setReceivedMoney(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getReceivablesDealMoney()==null){
                crmPutinStatisticsList.get(i).setReceivablesDealMoney(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getContractAllMoney()==null){
                crmPutinStatisticsList.get(i).setContractAllMoney(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getContractAllMoneyNotime()==null){
                crmPutinStatisticsList.get(i).setContractAllMoneyNotime(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getReceivablesDealMoneyNoTime()==null){
                crmPutinStatisticsList.get(i).setReceivablesDealMoneyNoTime(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getActualConsumptionMoney()==null){
                crmPutinStatisticsList.get(i).setActualConsumptionMoney(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getPracticalPutinMoney()==null){
                crmPutinStatisticsList.get(i).setPracticalPutinMoney(BigDecimal.valueOf(0));
            }

            summoney.setReceivedMoney(crmPutinStatisticsList.get(i).getReceivedMoney().add(summoney.getReceivedMoney()));
            summoney.setReceivablesDealMoney(crmPutinStatisticsList.get(i).getReceivablesDealMoney().add(summoney.getReceivablesDealMoney()));
            summoney.setContractAllMoney(crmPutinStatisticsList.get(i).getContractAllMoney().add(summoney.getContractAllMoney()));
            summoney.setContractAllMoneyNotime(crmPutinStatisticsList.get(i).getContractAllMoneyNotime().add(summoney.getContractAllMoneyNotime()));
            summoney.setReceivablesDealMoneyNoTime(crmPutinStatisticsList.get(i).getReceivablesDealMoneyNoTime().add(summoney.getReceivablesDealMoneyNoTime()));
            summoney.setActualConsumptionMoney(crmPutinStatisticsList.get(i).getActualConsumptionMoney().add(summoney.getActualConsumptionMoney()));
            summoney.setPracticalPutinMoney(crmPutinStatisticsList.get(i).getPracticalPutinMoney().add(summoney.getPracticalPutinMoney()));

            //计算第一套ROI
            if(crmPutinStatisticsList.get(i).getContractAllMoney()!=null && crmPutinStatisticsList.get(i).getContractAllMoney().compareTo(BigDecimal.ZERO)!=0) {
                if(crmPutinStatisticsList.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月实际合同/渠道消耗金额
                    crmPutinStatisticsList.get(i).setBdsjRoi(String.valueOf(crmPutinStatisticsList.get(i).getContractAllMoney().divide(crmPutinStatisticsList.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }
                if(crmPutinStatisticsList.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月实际合同/实际消耗金额
                    crmPutinStatisticsList.get(i).setBdzmRoi(String.valueOf(crmPutinStatisticsList.get(i).getContractAllMoney().divide(crmPutinStatisticsList.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
            if (crmPutinStatisticsList.get(i).getContractAllMoneyNotime()!=null && crmPutinStatisticsList.get(i).getContractAllMoneyNotime().compareTo(BigDecimal.ZERO)!=0){
                if (crmPutinStatisticsList.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月合同/渠道消耗金额
                    crmPutinStatisticsList.get(i).setCjkhRoi(String.valueOf(crmPutinStatisticsList.get(i).getContractAllMoneyNotime().divide(crmPutinStatisticsList.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }
                if(crmPutinStatisticsList.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月合同/实际消耗金额
                    crmPutinStatisticsList.get(i).setCjhtRoi(String.valueOf(crmPutinStatisticsList.get(i).getContractAllMoneyNotime().divide(crmPutinStatisticsList.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
            if(crmPutinStatisticsList.get(i).getReceivablesDealMoney()!=null && crmPutinStatisticsList.get(i).getReceivablesDealMoney().compareTo(BigDecimal.ZERO)!=0){
                if(crmPutinStatisticsList.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月实际回款/渠道消耗ROI
                    crmPutinStatisticsList.get(i).setSjdqdRoi(String.valueOf(crmPutinStatisticsList.get(i).getReceivablesDealMoney().divide(crmPutinStatisticsList.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }
                if (crmPutinStatisticsList.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月实际回款/实际消耗ROI
                    crmPutinStatisticsList.get(i).setSjdsjRoi(String.valueOf(crmPutinStatisticsList.get(i).getReceivablesDealMoney().divide(crmPutinStatisticsList.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
            if (crmPutinStatisticsList.get(i).getReceivablesDealMoneyNoTime()!=null && crmPutinStatisticsList.get(i).getReceivablesDealMoneyNoTime().compareTo(BigDecimal.ZERO)!=0){
                if (crmPutinStatisticsList.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月回款/渠道消耗ROI
                    crmPutinStatisticsList.get(i).setAllqdRoi(String.valueOf(crmPutinStatisticsList.get(i).getReceivablesDealMoneyNoTime().divide(crmPutinStatisticsList.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }
                if (crmPutinStatisticsList.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    // 月回款/实际消耗ROI
                    crmPutinStatisticsList.get(i).setAllsjRoi(String.valueOf(crmPutinStatisticsList.get(i).getReceivablesDealMoneyNoTime().divide(crmPutinStatisticsList.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
        }
        //计算第一套sumROI
        if(summoney.getContractAllMoney()!=null && summoney.getContractAllMoney().compareTo(BigDecimal.ZERO)!=0) {
            if(summoney.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月实际合同/渠道消耗金额
                summoney.setBdsjRoi(String.valueOf(summoney.getContractAllMoney().divide(summoney.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if(summoney.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月实际合同/实际消耗金额
                summoney.setBdzmRoi(String.valueOf(summoney.getContractAllMoney().divide(summoney.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }
        if (summoney.getContractAllMoneyNotime()!=null && summoney.getContractAllMoneyNotime().compareTo(BigDecimal.ZERO)!=0){
            if (summoney.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月合同/渠道消耗金额
                summoney.setCjkhRoi(String.valueOf(summoney.getContractAllMoneyNotime().divide(summoney.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if(summoney.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月合同/实际消耗金额
                summoney.setCjhtRoi(String.valueOf(summoney.getContractAllMoneyNotime().divide(summoney.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }
        if(summoney.getReceivablesDealMoney()!=null && summoney.getReceivablesDealMoney().compareTo(BigDecimal.ZERO)!=0){
            if(summoney.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月实际回款/渠道消耗ROI
                summoney.setSjdqdRoi(String.valueOf(summoney.getReceivablesDealMoney().divide(summoney.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if (summoney.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月实际回款/实际消耗ROI
                summoney.setSjdsjRoi(String.valueOf(summoney.getReceivablesDealMoney().divide(summoney.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }
        if (summoney.getReceivablesDealMoneyNoTime()!=null && summoney.getReceivablesDealMoneyNoTime().compareTo(BigDecimal.ZERO)!=0){
            if (summoney.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月回款/渠道消耗ROI
                summoney.setAllqdRoi(String.valueOf(summoney.getReceivablesDealMoneyNoTime().divide(summoney.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if (summoney.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                // 月回款/实际消耗ROI
                summoney.setAllsjRoi(String.valueOf(summoney.getReceivablesDealMoneyNoTime().divide(summoney.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }

        //计算第二套ROI(年份)
        List<CrmPutinStatistics> allRoiMoney=getBaseMapper().queryAllRoiMoney(search);
        List<CrmPutinStatistics> yearRoiMoney=getBaseMapper().queryYearMoney(search);

        //计算第二套sumROI(年份)
        CrmPutinStatistics summoney2=new CrmPutinStatistics();
        summoney2.setReceivablesDealMoneyNoTime(BigDecimal.valueOf(0));
        summoney2.setPracticalPutinMoney(BigDecimal.valueOf(0));
        summoney2.setContractAllMoneyNotime(BigDecimal.valueOf(0));
        summoney2.setActualConsumptionMoney(BigDecimal.valueOf(0));
        summoney2.setContractAllMoneyYear(BigDecimal.valueOf(0));
        summoney2.setReceivablesDealMoneyYear(BigDecimal.valueOf(0));

        //计算合同,回款相关金额
        for (int i=0;i<allRoiMoney.size();i++){
            for(int j=0;j<yearRoiMoney.size();j++){
                if (allRoiMoney.get(i).getPutinPlatform().equals(yearRoiMoney.get(j).getPutinPlatform())){
                    allRoiMoney.get(i).setContractAllMoneyYear(yearRoiMoney.get(j).getContractAllMoneyYear());
                    allRoiMoney.get(i).setReceivablesDealMoneyYear(yearRoiMoney.get(j).getReceivablesDealMoneyYear());
                }
            }
            BigDecimal money1 = allRoiMoney.get(i).getPracticalPutinMoney().subtract(allRoiMoney.get(i).getPutinRebatesMoney());
            BigDecimal money2 = money1.subtract(allRoiMoney.get(i).getPutinCompensateMoney());
            BigDecimal money3 = money2.subtract(allRoiMoney.get(i).getRolloutMoney());
            BigDecimal consumeMoney = money3.add(allRoiMoney.get(i).getRollinMoney());
            allRoiMoney.get(i).setActualConsumptionMoney(consumeMoney);

            for (int j=0;j<crmPutinStatisticsList.size();j++){
                if (allRoiMoney.get(i).getPutinPlatform().equals(crmPutinStatisticsList.get(j).getPutinPlatform())){
                    allRoiMoney.get(i).setReceivedMoney(crmPutinStatisticsList.get(j).getReceivedMoney());
                    allRoiMoney.get(i).setContractDealCount(crmPutinStatisticsList.get(j).getContractDealCount());
                    allRoiMoney.get(i).setReceivablesDealMoney(crmPutinStatisticsList.get(j).getReceivablesDealMoney());
                    allRoiMoney.get(i).setContractAllMoney(crmPutinStatisticsList.get(j).getContractAllMoney());
                }
            }
            for (int j=0;j<noTimeMoney.size();j++){
                if (noTimeMoney.get(j).getPutinPlatform().equals(allRoiMoney.get(i).getPutinPlatform())){
                    allRoiMoney.get(i).setContractAllMoneyNotime(noTimeMoney.get(j).getContractAllMoneyNotime());
                    allRoiMoney.get(i).setReceivablesDealMoneyNoTime(noTimeMoney.get(j).getReceivablesDealMoneyNoTime());
                }
            }
            if (allRoiMoney.get(i).getReceivablesDealMoneyNoTime()==null){
                allRoiMoney.get(i).setReceivablesDealMoneyNoTime(BigDecimal.valueOf(0));
            }
            if (allRoiMoney.get(i).getContractAllMoneyNotime()==null){
                allRoiMoney.get(i).setContractAllMoneyNotime(BigDecimal.valueOf(0));
            }
            if (allRoiMoney.get(i).getActualConsumptionMoney()==null){
                allRoiMoney.get(i).setActualConsumptionMoney(BigDecimal.valueOf(0));
            }
            if (allRoiMoney.get(i).getPracticalPutinMoney()==null){
                allRoiMoney.get(i).setPracticalPutinMoney(BigDecimal.valueOf(0));
            }
            if (allRoiMoney.get(i).getContractAllMoneyYear()==null){
                allRoiMoney.get(i).setContractAllMoneyYear(BigDecimal.valueOf(0));
            }
            if (allRoiMoney.get(i).getContractAllMoneyYear()==null){
                allRoiMoney.get(i).setContractAllMoneyYear(BigDecimal.valueOf(0));
            }
            if (allRoiMoney.get(i).getReceivablesDealMoneyYear()==null){
                allRoiMoney.get(i).setReceivablesDealMoneyYear(BigDecimal.valueOf(0));
            }

            //计算第二套ROI(年份ROI)
            if(allRoiMoney.get(i).getReceivablesDealMoneyNoTime()!=null && allRoiMoney.get(i).getContractAllMoneyNotime().compareTo(BigDecimal.ZERO)!=0) {
                if(allRoiMoney.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setBdsjRoi2(String.valueOf(allRoiMoney.get(i).getContractAllMoneyYear().divide(allRoiMoney.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }

                if(allRoiMoney.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setBdzmRoi2(String.valueOf(allRoiMoney.get(i).getContractAllMoneyYear().divide(allRoiMoney.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
            if (allRoiMoney.get(i).getContractAllMoneyYear()!=null && allRoiMoney.get(i).getContractAllMoneyNotime().compareTo(BigDecimal.ZERO)!=0){
                if (allRoiMoney.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setCjkhRoi2(String.valueOf(allRoiMoney.get(i).getContractAllMoneyYear().divide(allRoiMoney.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }


                if(allRoiMoney.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setCjhtRoi2(String.valueOf(allRoiMoney.get(i).getContractAllMoneyYear().divide(allRoiMoney.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
            if(allRoiMoney.get(i).getReceivablesDealMoneyNoTime()!=null && allRoiMoney.get(i).getReceivablesDealMoneyNoTime().compareTo(BigDecimal.ZERO)!=0){
                if(allRoiMoney.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setSjdqdRoi2(String.valueOf(allRoiMoney.get(i).getReceivablesDealMoneyYear().divide(allRoiMoney.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }
                if (allRoiMoney.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setSjdsjRoi2(String.valueOf(allRoiMoney.get(i).getReceivablesDealMoneyYear().divide(allRoiMoney.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
            if (allRoiMoney.get(i).getReceivablesDealMoneyYear()!=null && allRoiMoney.get(i).getReceivablesDealMoneyNoTime().compareTo(BigDecimal.ZERO)!=0){
                if (allRoiMoney.get(i).getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setAllqdRoi2(String.valueOf(allRoiMoney.get(i).getReceivablesDealMoneyYear().divide(allRoiMoney.get(i).getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
                }
                if (allRoiMoney.get(i).getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                    allRoiMoney.get(i).setAllsjRoi2(String.valueOf(allRoiMoney.get(i).getReceivablesDealMoneyYear().divide(allRoiMoney.get(i).getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
                }
            }
            //存入第二套ROI(年份ROI)
            for (int j=0;j<crmPutinStatisticsList.size();j++){
                if (allRoiMoney.get(i).getPutinPlatform().equals(crmPutinStatisticsList.get(j).getPutinPlatform())){
                    crmPutinStatisticsList.get(j).setAllqdRoi2(allRoiMoney.get(i).getAllqdRoi2());
                    crmPutinStatisticsList.get(j).setAllsjRoi2(allRoiMoney.get(i).getAllsjRoi2());
                    crmPutinStatisticsList.get(j).setBdsjRoi2(allRoiMoney.get(i).getBdsjRoi2());
                    crmPutinStatisticsList.get(j).setBdzmRoi2(allRoiMoney.get(i).getBdzmRoi2());
                    crmPutinStatisticsList.get(j).setSjdqdRoi2(allRoiMoney.get(i).getSjdqdRoi2());
                    crmPutinStatisticsList.get(j).setSjdsjRoi2(allRoiMoney.get(i).getSjdsjRoi2());
                    crmPutinStatisticsList.get(j).setCjhtRoi2(allRoiMoney.get(i).getCjhtRoi2());
                    crmPutinStatisticsList.get(j).setCjkhRoi2(allRoiMoney.get(i).getCjkhRoi2());
                }
            }
            summoney2.setReceivablesDealMoneyNoTime(allRoiMoney.get(i).getReceivablesDealMoneyNoTime().add(summoney2.getReceivablesDealMoneyNoTime()));
            summoney2.setPracticalPutinMoney(allRoiMoney.get(i).getPracticalPutinMoney().add( summoney2.getPracticalPutinMoney()));
            summoney2.setContractAllMoneyNotime(allRoiMoney.get(i).getContractAllMoneyNotime().add(summoney2.getContractAllMoneyNotime()));
            summoney2.setActualConsumptionMoney(allRoiMoney.get(i).getActualConsumptionMoney().add(summoney2.getActualConsumptionMoney()));
            summoney2.setContractAllMoneyYear(allRoiMoney.get(i).getContractAllMoneyYear().add(summoney2.getContractAllMoneyYear()));
            summoney2.setReceivablesDealMoneyYear(allRoiMoney.get(i).getReceivablesDealMoneyYear().add(summoney2.getReceivablesDealMoneyYear()));

        }

        //计算第二套sumROI
        if(summoney2.getReceivablesDealMoneyNoTime()!=null && summoney2.getContractAllMoneyNotime().compareTo(BigDecimal.ZERO)!=0) {
            if(summoney2.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setBdsjRoi2(String.valueOf(summoney2.getContractAllMoneyYear().divide(summoney2.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if(summoney2.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setBdzmRoi2(String.valueOf(summoney2.getContractAllMoneyYear().divide(summoney2.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }
        if (summoney2.getContractAllMoneyYear()!=null && summoney2.getContractAllMoneyNotime().compareTo(BigDecimal.ZERO)!=0){
            if (summoney2.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setCjkhRoi2(String.valueOf(summoney2.getContractAllMoneyYear().divide(summoney2.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if(summoney2.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setCjhtRoi2(String.valueOf(summoney2.getContractAllMoneyYear().divide(summoney2.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }
        if(summoney2.getReceivablesDealMoneyNoTime()!=null && summoney2.getReceivablesDealMoneyNoTime().compareTo(BigDecimal.ZERO)!=0){
            if(summoney2.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setSjdqdRoi2(String.valueOf(summoney2.getReceivablesDealMoneyYear().divide(summoney2.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if (summoney2.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setSjdsjRoi2(String.valueOf(summoney2.getReceivablesDealMoneyYear().divide(summoney2.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }
        if (summoney2.getReceivablesDealMoneyYear()!=null && summoney2.getReceivablesDealMoneyNoTime().compareTo(BigDecimal.ZERO)!=0){
            if (summoney2.getPracticalPutinMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setAllqdRoi2(String.valueOf(summoney2.getReceivablesDealMoneyYear().divide(summoney2.getPracticalPutinMoney(), 2, RoundingMode.HALF_UP)));
            }
            if (summoney2.getActualConsumptionMoney().compareTo(BigDecimal.ZERO)!=0) {
                summoney2.setAllsjRoi2(String.valueOf(summoney2.getReceivablesDealMoneyYear().divide(summoney2.getActualConsumptionMoney(), 2, RoundingMode.HALF_UP)));
            }
        }
        // 年合同/渠道消耗金额
        summoney.setCjkhRoi2(summoney2.getCjkhRoi2());
        // 年合同/实际消耗金额
        summoney.setCjhtRoi2(summoney2.getCjhtRoi2());
        // 年实际回款/实际消耗
        summoney.setSjdsjRoi2(summoney2.getSjdsjRoi2());
        // 年实际回款/渠道消耗
        summoney.setSjdqdRoi2(summoney2.getSjdqdRoi2());
        //年实际合同/实际消耗金额
        summoney.setBdzmRoi2(summoney2.getBdzmRoi2());
        // 年实际合同/渠道消耗金额
        summoney.setBdsjRoi2(summoney2.getBdsjRoi2());
        // 年回款/实际消耗ROI2
        summoney.setAllsjRoi2(summoney2.getAllsjRoi2());
        // 年回款/渠道消耗ROI
        summoney.setAllqdRoi2(summoney2.getAllqdRoi2());

        //空值显示为0
        for (int i=0;i<crmPutinStatisticsList.size();i++){
            if (crmPutinStatisticsList.get(i).getAllqdRoi()==null){
                crmPutinStatisticsList.get(i).setAllqdRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getAllsjRoi()==null){
                crmPutinStatisticsList.get(i).setAllsjRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getBdsjRoi()==null){
                crmPutinStatisticsList.get(i).setBdsjRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getBdzmRoi()==null){
                crmPutinStatisticsList.get(i).setBdzmRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getCjhtRoi()==null){
                crmPutinStatisticsList.get(i).setCjhtRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getCjkhRoi()==null){
                crmPutinStatisticsList.get(i).setCjkhRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getSjdqdRoi()==null){
                crmPutinStatisticsList.get(i).setSjdqdRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getSjdsjRoi()==null){
                crmPutinStatisticsList.get(i).setSjdsjRoi("0");
            }
            if (crmPutinStatisticsList.get(i).getContractDealCount()==null){
                crmPutinStatisticsList.get(i).setContractDealCount(0);
            }
            if (crmPutinStatisticsList.get(i).getReceivablesDealMoney()==null){
                crmPutinStatisticsList.get(i).setReceivablesDealMoney(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getReceivedMoney()==null){
                crmPutinStatisticsList.get(i).setReceivedMoney(BigDecimal.valueOf(0));
            }
            if (crmPutinStatisticsList.get(i).getAllqdRoi2()==null){
                crmPutinStatisticsList.get(i).setAllqdRoi2("0");
            }
            if (crmPutinStatisticsList.get(i).getAllsjRoi2()==null){
                crmPutinStatisticsList.get(i).setAllsjRoi2("0");
            }
            if (crmPutinStatisticsList.get(i).getBdsjRoi2()==null){
                crmPutinStatisticsList.get(i).setBdsjRoi2("0");
            }
            if (crmPutinStatisticsList.get(i).getBdzmRoi2()==null){
                crmPutinStatisticsList.get(i).setBdzmRoi2("0");
            }
            if (crmPutinStatisticsList.get(i).getCjhtRoi2()==null){
                crmPutinStatisticsList.get(i).setCjhtRoi2("0");
            }
            if (crmPutinStatisticsList.get(i).getCjkhRoi2()==null){
                crmPutinStatisticsList.get(i).setCjkhRoi2("0");
            }
            if (crmPutinStatisticsList.get(i).getSjdqdRoi2()==null){
                crmPutinStatisticsList.get(i).setSjdqdRoi2("0");
            }
            if (crmPutinStatisticsList.get(i).getSjdsjRoi2()==null){
                crmPutinStatisticsList.get(i).setSjdsjRoi2("0");
            }
        }

//        JSONObject sourceMap = this.crmSourceMapper.querySourceDataGroupByCode();

        for (int i=0;i<crmPutinStatisticsList.size();i++){
//            contractList.get(i).setCategory(String.valueOf(sourceMap.get(contractList.get(i).getCategories())));
            if (crmPutinStatisticsList.get(i).getCategories()==1){
                crmPutinStatisticsList.get(i).setCategory("电商美工");
            }else if (crmPutinStatisticsList.get(i).getCategories()==2){
                crmPutinStatisticsList.get(i).setCategory("手绘插画");
            }else if (crmPutinStatisticsList.get(i).getCategories()==3){
                crmPutinStatisticsList.get(i).setCategory("影视后期");
            }
        }
        //根据字段排序
        if (search.getSortField() !=null && search.getSortField().equals("receivablesDealMoney")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(String.valueOf(o1.getReceivablesDealMoney())).compareTo(new Double(String.valueOf(o2.getReceivablesDealMoney()))) == 0) {
                            return -1;
                        }else {
                            return +1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(String.valueOf(o1.getReceivablesDealMoney())).compareTo(new Double(String.valueOf(o2.getReceivablesDealMoney()))) == 0) {
                            return +1;
                        }
                    }
                    return new Double(String.valueOf(o2.getReceivablesDealMoney())).compareTo(new Double(String.valueOf(o1.getReceivablesDealMoney())));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("actualConsumptionMoney")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(String.valueOf(o1.getActualConsumptionMoney())).compareTo(new Double(String.valueOf(o2.getActualConsumptionMoney()))) == 0) {
                            return -1;
                        }else {
                            return +1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(String.valueOf(o1.getActualConsumptionMoney())).compareTo(new Double(String.valueOf(o2.getActualConsumptionMoney()))) == 0) {
                            return +1;
                        }
                    }
                    return new Double(String.valueOf(o2.getActualConsumptionMoney())).compareTo(new Double(String.valueOf(o1.getActualConsumptionMoney())));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("contractDealCount")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(String.valueOf(o1.getContractDealCount())).compareTo(new Double(String.valueOf(o2.getContractDealCount()))) == 0) {
                            return -1;
                        }else {
                            return +1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(String.valueOf(o1.getContractDealCount())).compareTo(new Double(String.valueOf(o2.getContractDealCount()))) == 0) {
                            return +1;
                        }
                    }
                    return new Double(String.valueOf(o2.getContractDealCount())).compareTo(new Double(String.valueOf(o1.getContractDealCount())));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("receivedMoney")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(String.valueOf(o1.getReceivedMoney())).compareTo(new Double(String.valueOf(o2.getReceivedMoney()))) == 0) {
                            return -1;
                        }else {
                            return +1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(String.valueOf(o1.getReceivedMoney())).compareTo(new Double(String.valueOf(o2.getReceivedMoney()))) == 0) {
                            return +1;
                        }
                    }
                    return new Double(String.valueOf(o2.getReceivedMoney())).compareTo(new Double(String.valueOf(o1.getReceivedMoney())));
                }
            });
        }

        if (search.getSortField() !=null && search.getSortField().equals("contractAllMoney")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(String.valueOf(o1.getContractAllMoney())).compareTo(new Double(String.valueOf(o2.getContractAllMoney()))) == 0) {
                            return -1;
                        }else {
                            return +1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(String.valueOf(o1.getContractAllMoney())).compareTo(new Double(String.valueOf(o2.getContractAllMoney()))) == 0) {
                            return +1;
                        }
                    }
                    return new Double(String.valueOf(o2.getContractAllMoney())).compareTo(new Double(String.valueOf(o1.getContractAllMoney())));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("receivablesDealMoney")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(String.valueOf(o1.getReceivablesDealMoney())).compareTo(new Double(String.valueOf(o2.getReceivablesDealMoney()))) == 0) {
                            return -1;
                        }else {
                            return +1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(String.valueOf(o1.getReceivablesDealMoney())).compareTo(new Double(String.valueOf(o2.getReceivablesDealMoney()))) == 0) {
                            return +1;
                        }
                    }
                    return new Double(String.valueOf(o2.getReceivablesDealMoney())).compareTo(new Double(String.valueOf(o1.getReceivablesDealMoney())));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("sjdqdRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getSjdqdRoi()).compareTo(new Double(o2.getSjdqdRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getSjdqdRoi()).compareTo(new Double(o2.getSjdqdRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getSjdqdRoi()).compareTo(new Double(o1.getSjdqdRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("sjdsjRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getSjdsjRoi()).compareTo(new Double(o2.getSjdsjRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getSjdsjRoi()).compareTo(new Double(o2.getSjdsjRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getSjdsjRoi()).compareTo(new Double(o1.getSjdsjRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("allqdRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getAllqdRoi()).compareTo(new Double(o2.getAllqdRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getAllqdRoi()).compareTo(new Double(o2.getAllqdRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getAllqdRoi()).compareTo(new Double(o1.getAllqdRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("allsjRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getAllsjRoi()).compareTo(new Double(o2.getAllsjRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getAllsjRoi()).compareTo(new Double(o2.getAllsjRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getAllsjRoi()).compareTo(new Double(o1.getAllsjRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("bdsjRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getBdsjRoi()).compareTo(new Double(o2.getBdsjRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getBdsjRoi()).compareTo(new Double(o2.getBdsjRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getBdsjRoi()).compareTo(new Double(o1.getBdsjRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("bdzmRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getBdzmRoi()).compareTo(new Double(o2.getBdzmRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getBdzmRoi()).compareTo(new Double(o2.getBdzmRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getBdzmRoi()).compareTo(new Double(o1.getBdzmRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("cjkhRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getCjkhRoi()).compareTo(new Double(o2.getCjkhRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getCjkhRoi()).compareTo(new Double(o2.getCjkhRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getCjkhRoi()).compareTo(new Double(o1.getCjkhRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("cjhtRoi")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getCjhtRoi()).compareTo(new Double(o2.getCjhtRoi())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getCjhtRoi()).compareTo(new Double(o2.getCjhtRoi())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getCjhtRoi()).compareTo(new Double(o1.getCjhtRoi()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("sjdqdRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getSjdqdRoi2()).compareTo(new Double(o2.getSjdqdRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getSjdqdRoi2()).compareTo(new Double(o2.getSjdqdRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getSjdqdRoi2()).compareTo(new Double(o1.getSjdqdRoi2()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("sjdsjRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getSjdsjRoi2()).compareTo(new Double(o2.getSjdsjRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getSjdsjRoi2()).compareTo(new Double(o2.getSjdsjRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getSjdsjRoi2()).compareTo(new Double(o1.getSjdsjRoi2()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("allqdRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getAllqdRoi2()).compareTo(new Double(o2.getAllqdRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getAllqdRoi2()).compareTo(new Double(o2.getAllqdRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getAllqdRoi2()).compareTo(new Double(o1.getAllqdRoi2()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("allsjRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getAllsjRoi2()).compareTo(new Double(o2.getAllsjRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getAllsjRoi2()).compareTo(new Double(o2.getAllsjRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getAllsjRoi2()).compareTo(new Double(o1.getAllsjRoi2()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("bdsjRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getBdsjRoi2()).compareTo(new Double(o2.getBdsjRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getBdsjRoi2()).compareTo(new Double(o2.getBdsjRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getBdsjRoi2()).compareTo(new Double(o1.getBdsjRoi2()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("bdzmRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getBdzmRoi2()).compareTo(new Double(o2.getBdzmRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getBdzmRoi2()).compareTo(new Double(o2.getBdzmRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getBdzmRoi2()).compareTo(new Double(o1.getBdzmRoi2()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("cjkhRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getCjkhRoi2()).compareTo(new Double(o2.getCjkhRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getCjkhRoi2()).compareTo(new Double(o2.getCjkhRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getCjkhRoi2()).compareTo(new Double(o1.getCjkhRoi2()));
                }
            });
        }
        if (search.getSortField() !=null && search.getSortField().equals("cjhtRoi2")) {
            Collections.sort(crmPutinStatisticsList, new Comparator<CrmPutinStatistics>() {
                @Override
                public int compare(CrmPutinStatistics o1, CrmPutinStatistics o2) {
                    if (search.getOrder()==1) {
                        if (new Double(o1.getCjhtRoi2()).compareTo(new Double(o2.getCjhtRoi2())) == 0) {
                            return +1;
                        }else {
                            return -1;
                        }
                    }else if (search.getOrder()==2){
                        if (new Double(o1.getCjhtRoi2()).compareTo(new Double(o2.getCjhtRoi2())) == 0) {
                            return +1;
                        }
                    }
                    return new Double(o2.getCjhtRoi2()).compareTo(new Double(o1.getCjhtRoi2()));
                }
            });
        }
        /*for (int i=0;i<contractList.size();i++){
            for (int j=0;j<crmPutinStatisticsList.size();j++){
                if (StringUtil.isNotBlank(contractList.get(i).getPutinPlatform()) && StringUtil.isNotBlank(crmPutinStatisticsList.get(j).getPutinPlatform()) && contractList.get(i).getPutinPlatform().equals(crmPutinStatisticsList.get(j).getPutinPlatform())){
                    crmPutinStatisticsList.get(j).setCategory(contractList.get(i).getCategory());
                }
            }
        }*/
        BasePage<CrmPutinStatistics> basePage = new BasePage<>(search.getPage(),search.getLimit(),count);
        basePage.setList(crmPutinStatisticsList);
        basePage.setTotal(crmPutinStatisticsList.size());
        basePage.setExtraData(summoney);
        return basePage;
    }

    public List<CrmPutinStatistics> queryNewList(CrmPutinStatistics crmPutinStatistics) {
        QueryWrapper<CrmPutinStatistics> queryWrapper = new QueryWrapper<>();
        List<CrmPutinStatistics> list = baseMapper.selectList(queryWrapper);
//        BasePage<Map<String, Object>> basePage = queryList(search);
        return list;
    }

    @Override
    public void addOrUpdate(CrmPutinStatistics crmPutinStatistics) {
        if(crmPutinStatistics.getPutinStatisticsId() != null){
            crmPutinStatistics.setUpdateTime(DateUtil.date());
            baseMapper.updateById(crmPutinStatistics);
        }else{
            crmPutinStatistics.setCreateTime(DateUtil.date());
            baseMapper.insert(crmPutinStatistics);
        }
    }

    @Override
    public void updateInformation(CrmUpdateInformationBO updateInformationBO) {

    }

    /**
     * 根据投放平台与课程期数查询投放统计数据
     * @param putinPlatform 投放平台
     * @param putinFormDate 表单录入时间
     * @return 投放统计数据集合
     */
    @Override
    public CrmPutinStatistics queryStatisticsByPlatformAndPutinDormData(String putinPlatform, Date putinFormDate){
        return crmPutinStatisticsMapper.queryStatisticsByPlatformAndPutinDormData(putinPlatform,putinFormDate);
    }

    @Override
    public CrmPutinStatistics queryStatisticsByPlatformAndPutinDormData(String putinPlatform, String putinFormDate){
        return crmPutinStatisticsMapper.queryStatisticsByPlatformAndPutinDormData(putinPlatform,putinFormDate);
    }

    @Override
    public void roiSatistics(){

        THREAD_POOL.execute(() -> {

            long roiStartTime = System.currentTimeMillis();
            QueryWrapper<CrmPutinStatistics> queryWrapper = new QueryWrapper<>();
            List<CrmPutinStatistics> list = baseMapper.selectList(queryWrapper);
            if(CollectionUtil.isEmpty(list)){
                return;
            }
            for(CrmPutinStatistics statistics : list){
                String qishu = statistics.getCoursePeriods();
                String laiyuan = statistics.getPutinPlatform();
                BigDecimal practicalPutinMoney = statistics.getPracticalPutinMoney();
                long startTime = System.currentTimeMillis();
                try {
                    //有效表单数
                    Integer validNum = SpringUtils.getBean(ICrmLeadsService.class).validDataNum(laiyuan, qishu);
                    //加客服数
                    Integer addServiceNum = SpringUtils.getBean(ICrmLeadsService.class).addCustomerServiceNum(laiyuan, qishu);
                    //报课数
                    Integer addClassNum = SpringUtils.getBean(ICrmLeadsService.class).addClassReportNum(laiyuan, qishu);
                    //大额数量
                    Integer bigNum = SpringUtils.getBean(ICrmContractService.class).largeAmountNum(laiyuan, qishu);
                    //大额成交量，大额成交金额
                    Map<String, Object> map = SpringUtils.getBean(ICrmContractService.class).bigDealSum(laiyuan, qishu);
                    //大额成交量
                    Integer bigDealNum = 0;
                    //大额成交金额
                    BigDecimal bigDealMonery = new BigDecimal(0.0);

                    if(Optional.ofNullable(map).isPresent() && Optional.ofNullable(map.get("num")).isPresent()){
                        bigDealNum = Integer.valueOf(map.get("num").toString());
                    }

                    if(Optional.ofNullable(map).isPresent() && Optional.ofNullable(map.get("money")).isPresent()){
                        bigDealMonery = new BigDecimal(map.get("money").toString());
                    }
                    //  `leads_entering_form` int(9) NOT NULL DEFAULT '0' COMMENT '线索录入有效表单',
                    //  `add_customer_service_num` int(9) NOT NULL DEFAULT '0' COMMENT '加客服数量',
                    //  `add_customer_service_rate` varchar(255) DEFAULT NULL COMMENT '加客服率',
                    //  `small_amount_num` int(9) DEFAULT '0' COMMENT '小额报课数量',
                    //  `small_amount_rate` varchar(255) DEFAULT NULL COMMENT '小额报课率',
                    //  `large_amount_turnover` int(9) DEFAULT '0' COMMENT '大额成交数',
                    //  `large_amount_turnover_rate` varchar(255) DEFAULT NULL COMMENT '大额成交率',
                    //  `large_amount_turnover_money` decimal(10,2) DEFAULT '0.00' COMMENT '大额成交金额',
                    //  `current_period_roi` varchar(255) DEFAULT NULL COMMENT '当期ROI',

//                BigDecimal addCustomerServiceRate = new BigDecimal(addServiceNum / validNum).setScale(2);

                    BigDecimal addCustomerServiceRate = new BigDecimal(0.00);

                    BigDecimal smallAmountRate = new BigDecimal(0.00);

                    if(validNum.compareTo(0) > 0){
                        addCustomerServiceRate = new BigDecimal(addServiceNum).divide(new BigDecimal(validNum),2, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100));

                        smallAmountRate = new BigDecimal(addClassNum).divide(new BigDecimal(validNum),2, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100));
                    }

                    BigDecimal largeAmountTurnoverRate = new BigDecimal(0.00);

                    if(bigNum.compareTo(0) > 0){

                        largeAmountTurnoverRate =  new BigDecimal(bigDealNum).divide(new BigDecimal(bigNum),2, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100));
                    }

                    BigDecimal currentPeriodRoi = new BigDecimal(0.00);

                    if(practicalPutinMoney.compareTo(new BigDecimal(0.0)) != 0){
                        currentPeriodRoi = bigDealMonery.divide(practicalPutinMoney,2, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100));
                    }

                    LambdaUpdateWrapper<CrmPutinStatistics> updateWrapper = new LambdaUpdateWrapper<>();
                    //线索录入有效表单
                    updateWrapper.set(CrmPutinStatistics::getLeadsEnteringForm, validNum);
                    //加客服数量
                    updateWrapper.set(CrmPutinStatistics::getAddCustomerServiceNum, addServiceNum);
                    //加客服率
                    updateWrapper.set(CrmPutinStatistics::getAddCustomerServiceRate, addCustomerServiceRate + "%");
                    //小额报课数量
                    updateWrapper.set(CrmPutinStatistics::getSmallAmountNum, addClassNum);
                    //小额报课率
                    updateWrapper.set(CrmPutinStatistics::getSmallAmountRate, smallAmountRate + "%");
                    //大额成交数
                    updateWrapper.set(CrmPutinStatistics::getLargeAmountTurnover, bigDealNum);
                    //大额成交率
                    updateWrapper.set(CrmPutinStatistics::getLargeAmountTurnoverRate, largeAmountTurnoverRate + "%");
                    //大额成交金额
                    updateWrapper.set(CrmPutinStatistics::getLargeAmountTurnoverMoney, bigDealMonery);
                    //当期ROI
                    updateWrapper.set(CrmPutinStatistics::getCurrentPeriodRoi, currentPeriodRoi + "%");

                    updateWrapper.eq(CrmPutinStatistics::getPutinStatisticsId, statistics.getPutinStatisticsId());
                    update(updateWrapper);

                }catch(Exception e){
                    long endTime = System.currentTimeMillis();
                    long useTime = (endTime - startTime)/1000;
                    log.info("ROI统计异常：期数："+qishu+",平台："+laiyuan+",耗时："+useTime+"s!,异常信息：",e);
                    CrmInterfaceErrorInfoLog log = new CrmInterfaceErrorInfoLog().setInterName(CrmPutinStatisticsServiceImpl.class.getSimpleName())
                            .setInterMethod("roiSatistics")
                            .setInterParam(JSONObject.toJSONString(statistics))
                            .setCreateTime(new Date())
                            .setUseTime(useTime+"s")
                            .setErrorInfo(e.getMessage());
                    crmInterfaceErrorInfoLogMapper.insert(log);
                }

            }
            long roiEndTime = System.currentTimeMillis();
            log.info("ROI统计结束日期："+ DateUtil.formatDateTime(new Date())+",耗时："+((roiEndTime - roiStartTime)/1000)+"s!");
        });
    }





    public List<CrmFieldSortVO> queryListHead(){
        List<CrmFieldSortVO> list = new ArrayList<>();
        list.add(new CrmFieldSortVO().setName("投放平台").setType(FieldEnum.SELECT.getType()).setFieldName("putinPlatform").setFormType(FieldEnum.SELECT.getFormType()));
        list.add(new CrmFieldSortVO().setName("大类").setType(FieldEnum.TEXT.getType()).setFieldName("category").setFormType(FieldEnum.TEXT.getFormType()));
        list.add(new CrmFieldSortVO().setName("渠道充值金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("putinMoney").setFormType(FieldEnum.FLOATNUMBER.getFormType()));

        list.add(new CrmFieldSortVO().setName("渠道消耗金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("practicalPutinMoney").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("渠道返点金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("putinRebatesMoney").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("渠道赔付金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("putinCompensateMoney").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("实际消耗金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("actualConsumptionMoney").setFormType(FieldEnum.FLOATNUMBER.getFormType()));

        list.add(new CrmFieldSortVO().setName("投放开始日期").setType(FieldEnum.DATE.getType()).setFieldName("putinDateStart").setFormType(FieldEnum.DATE.getFormType()));
        list.add(new CrmFieldSortVO().setName("投放结束日期").setType(FieldEnum.DATE.getType()).setFieldName("putinDateEnd").setFormType(FieldEnum.DATE.getFormType()));

        list.add(new CrmFieldSortVO().setName("合同成交数量").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("contractDealCount").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("合同成交金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("contractAllMoney").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("回款成交金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("receivablesDealMoneyNoTime").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("实际回款成交金额").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("receivablesDealMoney").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("实际回款/渠道消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("sjdqdRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("实际回款/实际消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("sjdsjRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("回款/渠道消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("allqdRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("回款/实际消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("allsjRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("实际合同/渠道消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("bdsjRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("实际合同/实际消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("bdzmRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));

        list.add(new CrmFieldSortVO().setName("合同/渠道消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("cjkhRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("合同/实际消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("cjhtRoi").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年实际回款/渠道消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("sjdqdRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年实际回款/实际消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("sjdsjRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年回款/渠道消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("allqdRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年回款/实际消耗ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("allsjRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年实际合同/渠道消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("bdsjRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年实际合同/实际消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("bdzmRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年合同/渠道消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("cjkhRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));
        list.add(new CrmFieldSortVO().setName("年合同/实际消耗金额ROI").setType(FieldEnum.FLOATNUMBER.getType()).setFieldName("cjhtRoi2").setFormType(FieldEnum.FLOATNUMBER.getFormType()));

        return list;
    }


    /**
     * 导出
     *
     * @param response resp
     * @param search  搜索对象
     */
    @Override
    public void exportExcel(HttpServletResponse response, CrmRoiSearchBO search) {
        if (StringUtils.isNotBlank(search.getPutinPlatform())) {
            String allPutinPlatform = search.getPutinPlatform();
            search.setPutinPlatforms(allPutinPlatform.split(","));
        }
        BasePage<CrmPutinStatistics> crmPutinStatisticsBase = crmPutinStatisticsService.queryPageList(search);
        List<CrmPutinStatistics> crmPutinStatisticsList=crmPutinStatisticsBase.getList();
        List<Map<String, Object>> dataList = new ArrayList<>();
        if(CollectionUtil.isNotEmpty(crmPutinStatisticsList)){
            for(CrmPutinStatistics crmPutinStatistics : crmPutinStatisticsList){
                Map<String, Object> map = new HashMap();
                map.put("putinPlatform",crmPutinStatistics.getPutinPlatform());
                map.put("category",crmPutinStatistics.getCategory());
                map.put("putinMoney",crmPutinStatistics.getPutinMoney());
                map.put("practicalPutinMoney",crmPutinStatistics.getPracticalPutinMoney());
                map.put("putinRebatesMoney",crmPutinStatistics.getPutinRebatesMoney());
                map.put("putinCompensateMoney",crmPutinStatistics.getPutinCompensateMoney());
                map.put("actualConsumptionMoney",crmPutinStatistics.getActualConsumptionMoney());
                map.put("putinDateStart",null != crmPutinStatistics.getPutinDateStart()?DateUtil.format(crmPutinStatistics.getPutinDateStart(),"yyyy-MM-dd"):"-");
                map.put("putinDateEnd",null != crmPutinStatistics.getPutinDateEnd()?DateUtil.format(crmPutinStatistics.getPutinDateEnd(),"yyyy-MM-dd"):"-");
                map.put("contractDealCount",crmPutinStatistics.getContractDealCount());
                map.put("contractAllMoney",crmPutinStatistics.getContractAllMoney());
                map.put("receivedMoney",crmPutinStatistics.getReceivedMoney());
                map.put("receivablesDealMoney",crmPutinStatistics.getReceivablesDealMoney());
                map.put("receivablesDealMoneyNoTime",crmPutinStatistics.getReceivablesDealMoneyNoTime());
                map.put("sjdqdRoi",crmPutinStatistics.getSjdqdRoi());
                map.put("sjdsjRoi",crmPutinStatistics.getSjdsjRoi());
                map.put("allqdRoi",crmPutinStatistics.getAllqdRoi());
                map.put("allsjRoi",crmPutinStatistics.getAllsjRoi());
                map.put("bdsjRoi",crmPutinStatistics.getBdsjRoi());
                map.put("bdzmRoi",crmPutinStatistics.getBdzmRoi());
                map.put("cjkhRoi",crmPutinStatistics.getCjkhRoi());
                map.put("cjhtRoi",crmPutinStatistics.getCjhtRoi());

                map.put("sjdqdRoi2",crmPutinStatistics.getSjdqdRoi2());
                map.put("sjdsjRoi2",crmPutinStatistics.getSjdsjRoi2());
                map.put("allqdRoi2",crmPutinStatistics.getAllqdRoi2());
                map.put("allsjRoi2",crmPutinStatistics.getAllsjRoi2());
                map.put("bdsjRoi2",crmPutinStatistics.getBdsjRoi2());
                map.put("bdzmRoi2",crmPutinStatistics.getBdzmRoi2());
                map.put("cjkhRoi2",crmPutinStatistics.getCjkhRoi2());
                map.put("cjhtRoi2",crmPutinStatistics.getCjhtRoi2());

                dataList.add(map);
            }
        }
        try (ExcelWriter writer = ExcelUtil.getWriter()) {
            List<CrmFieldSortVO> headList = queryListHead();
            headList.forEach(head -> writer.addHeaderAlias(head.getFieldName(), head.getName()));
            writer.merge(headList.size() - 1, "ROI投入统计信息");
            if (dataList.size() == 0) {
                Map<String, Object> record = new HashMap<>();
                headList.forEach(head -> record.put(head.getFieldName(), ""));
                dataList.add(record);
            }
            writer.setOnlyAlias(true);
            writer.write(dataList, true);
            writer.setRowHeight(0, 30);
            writer.setRowHeight(1, 20);
            for (int i = 0; i < headList.size(); i++) {
                writer.setColumnWidth(i, 20);
            }
            Cell cell = writer.getCell(0, 0);
            CellStyle cellStyle = cell.getCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font font = writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            //自定义标题别名
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename=roi_putin_monery.xls");
            ServletOutputStream out = response.getOutputStream();

            writer.flush(out);
        } catch (Exception e) {
            log.error("导出ROI投放金额错误：", e);
        }
    }


    /**
     * 查询投放平台
     *
     */
    public List<String> querySource(){
        List<String> result = new ArrayList<>();
        QueryWrapper<CrmField> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("label", 1);
        queryWrapper.eq("field_name", "source");
        List<CrmField> fieldList = crmFieldMapper.selectList(queryWrapper);
        if(CollectionUtil.isNotEmpty(fieldList)){
            CrmField crmField = fieldList.get(0);
            String options = crmField.getOptions();
            if(StringUtils.isNotBlank(options)){
                String[] array = options.split(",");
                result = Arrays.asList(array);
            }
            return result;
        }else{
            return result;
        }
    }
}
